﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using WebTemplate.DAL.Entities;

namespace WebTemplate.DAL.Repositories
{
    public class StudentInCoursesRepository
    {
        public void Insert(StudentInCourse Item)
        {
            SqlConnection connecton = new SqlConnection();
            connecton.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connecton;
            command.CommandText = "[StudentInCourses_Insert]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@CourseSemesterId", Item.CourseSemesterId);
            command.Parameters.AddWithValue("@StudentId", Item.StudentId);
            command.Parameters.AddWithValue("@Grade", Item.Grade);
            connecton.Open();
            command.ExecuteNonQuery();
            connecton.Close();
        }

        public void Update(StudentInCourse Item)
        {
            SqlConnection connecton = new SqlConnection();
            connecton.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connecton;
            command.CommandText = "[StudentInCourses_Update]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@Id", Item.Id);
            command.Parameters.AddWithValue("@CourseSemesterId", Item.CourseSemesterId);
            command.Parameters.AddWithValue("@StudentId", Item.StudentId);
            command.Parameters.AddWithValue("@Grade", Item.Grade);
            connecton.Open();
            command.ExecuteNonQuery();
            connecton.Close();
        }

        public void Delete(int ID)
        {
            SqlConnection connecton = new SqlConnection();
            connecton.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connecton;
            command.CommandText = "[StudentInCourses_Delete]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@Id",Id);
            connecton.Open();
            command.ExecuteNonQuery();
            connecton.Close();
        }

        public List<StudentInCourse> Select(StudentInCourseSearchCriteria Item)
        {
            SqlConnection connecton = new SqlConnection();
            connecton.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connecton;
            command.CommandText = "[StudentInCourses_Select]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@Id", Item.Id);
            command.Parameters.AddWithValue("@CourseSemesterId", Item.CourseSemesterId);
            command.Parameters.AddWithValue("@StudentId", Item.StudentId);
            command.Parameters.AddWithValue("@Grade", Item.Grade);
            connecton.Open();
            SqlDataReader reader = command.ExecuteReader();
            List<StudentInCourse> reg = new List<StudentInCourse>();
            while (reader.Read())
            {
                StudentInCourse obj = new StudentInCourse();

                obj.Id = reader.GetInt32(0);
                obj.CourseSemesterId = reader.GetInt32(1);
                obj.StudentId = reader.GetInt32(2);
                obj.Grade = reader.GetInt32(3);

                reg.Add(obj);
            }
            
            connecton.Close();
            return reg;
        }

    }
}
